﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;

namespace QuickExcel
{
    public class Currency
    {

        public static object[,] GetLatestRates()
        {
            Excel.Range rngDestination;
            Excel.Worksheet ws;
            Excel.QueryTables qts;
            Excel.QueryTable qt;

            object[,] oLatestRates;

            Globals.ThisAddIn.Application.ScreenUpdating = false;
            
            ws = Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets.Add();
            ws.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden;

            rngDestination = ws.Range["A2"];
            qts = ws.QueryTables;
            qt = qts.Add(Connection:"URL;http://www.x-rates.com/cgi-bin/cgicalc.cgi?value=1&base=USD", Destination: rngDestination);
            qt.Name = "ExchangeRates";
            qt.FieldNames = true;
            qt.RowNumbers = false;
            qt.FillAdjacentFormulas = false;
            qt.PreserveFormatting = true;
            qt.RefreshOnFileOpen = false;
            qt.BackgroundQuery = false;
            qt.RefreshStyle = Excel.XlCellInsertionMode.xlOverwriteCells;
            qt.SavePassword = false;
            qt.SaveData = true;
            qt.AdjustColumnWidth = true;
            qt.RefreshPeriod = 0;
            qt.WebSelectionType = Excel.XlWebSelectionType.xlSpecifiedTables;
            qt.WebFormatting =  Excel.XlWebFormatting.xlWebFormattingNone;
            qt.WebTables = "14";
            qt.WebPreFormattedTextToColumns = true;
            qt.WebConsecutiveDelimitersAsOne = true;
            qt.WebSingleBlockTextImport = false;
            qt.WebDisableDateRecognition = false;
            qt.WebDisableRedirections = false;

            qt.Refresh(BackgroundQuery:false);

            if (ws.UsedRange.Cells.Count > 1)
            {
                ws.Cells[1, 1] = "U.S. Dollar";
                ws.Cells[1, 2] = 1;
                ws.Cells[1, 3] = "USD";

                oLatestRates = ws.UsedRange.Value2;

                for (int i = 1; i <= oLatestRates.GetLength(0); i++)
                {
                    oLatestRates[i, 1] = Globals.ThisAddIn.Application.WorksheetFunction.Trim(oLatestRates[i, 1].ToString());
                    oLatestRates[i, 2] = Globals.ThisAddIn.Application.WorksheetFunction.Trim(oLatestRates[i, 2].ToString());
                    oLatestRates[i, 3] = Globals.ThisAddIn.Application.WorksheetFunction.Trim(oLatestRates[i, 3].ToString());
                }
            }
            else
            {
                System.Windows.Forms.MessageBox.Show("It is not possible to get latest rates at this time \nbecause this device is not connected to internet.");
                oLatestRates = null;
            }
            
            qt.Delete();
            ws.UsedRange.Clear();
            Globals.ThisAddIn.Application.DisplayAlerts = false;
            ws.Visible = Excel.XlSheetVisibility.xlSheetHidden;
            ws.Delete();
            Globals.ThisAddIn.Application.DisplayAlerts = true;

            Globals.ThisAddIn.Application.ScreenUpdating = true;
            return oLatestRates;
        }

        public static void ConvertCurrency(string sFrom, object[,] arrFromValue, string sTo,object[,]oLatestRates)
        {
            double fromFactor, toFactor, dMultiplyer;            

            fromFactor = toFactor = 0;

            for (int i = 1; i <= oLatestRates.GetLength(0); i++)
            {
                if (oLatestRates[i, 1].ToString().Equals(sFrom))
                {
                    fromFactor = Convert.ToDouble(oLatestRates[i, 2]);
                    if (toFactor != 0)
                        break;
                    else
                        continue;
                }
                else if (oLatestRates[i, 1].ToString().Equals(sTo))
                {
                    toFactor = Convert.ToDouble(oLatestRates[i, 2]);
                    if (fromFactor != 0)
                        break;
                    else
                        continue;
                }  
            }

            dMultiplyer = toFactor / fromFactor;

            for (int i = 1; i <= arrFromValue.GetLength(0); i++)
                for (int j = 1; j <= arrFromValue.GetLength(1); j++)
                    arrFromValue[i, j] = (double)arrFromValue[i, j] * dMultiplyer;
        }
    }
}
